package com.jymj.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Created by ljj on 2017/8/8.
 */
public class ExcelUtil {

    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
        HSSFFont headfont;
        HSSFFont font;
        HSSFCellStyle style;
        // 设置字体
        headfont = wb.createFont();
        //headfont.setFontName("黑体");
        headfont.setFontHeightInPoints((short) 20);// 字体大小
        headfont.setBold(true);// 加粗
        // 设置字体
        font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 13);// 字体大小
        // 普通单元格样式
        style = wb.createCellStyle();
        style.setFont(font);
        // 左右居中
        //style.setAlignment(HorizontalAlignment.CENTER);
        //style.setVerticalAlignment(VerticalAlignment.CENTER);
        //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
        style.setWrapText(true);
        //style.setLeftBorderColor(HSSFColor.BLACK.index);
        //style.setBorderLeft((short) 1);
        //style.setRightBorderColor(HSSFColor.BLACK.index);
        //style.setBorderRight((short) 1);
        //style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
        //style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
        //style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．

        // 第一步，创建一个webbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        //HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        //style.setAlignment(HorizontalAlignment.CENTER);
        // style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFCell cell;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);

            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(style);
            }
        }
        //sheet.addMergedRegion(new CellRangeAddress(0,0,1,2));//首行
        sheet.setColumnWidth(0, 8000);
        sheet.setColumnWidth(1, 4500);
        sheet.setColumnWidth(2, 4500);
        sheet.setColumnWidth(3, 4500);
        // sheet.setDefaultRowHeightInPoints((float)500);
        return wb;
    }

    public static HSSFWorkbook getHSSFWorkbook1(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
        HSSFFont headfont;
        HSSFFont font;
        HSSFCellStyle style;
        // 设置字体
        headfont = wb.createFont();
        headfont.setFontName("黑体");
        headfont.setFontHeightInPoints((short) 14);// 字体大小
        headfont.setBold(true);// 加粗
        // 普通单元格样式
        style = wb.createCellStyle();
        style.setFont(headfont);
        // 左右居中
        // style.setAlignment(HorizontalAlignment.CENTER);
        //style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        //style.setLeftBorderColor(HSSFColor.BLACK.index);
        //style.setBorderLeft((short) 1);
        //style.setRightBorderColor(HSSFColor.BLACK.index);
        //style.setBorderRight((short) 1);
        //style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
        //style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
        //style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．

        // 第一步，创建一个webbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置高度
        sheet.setDefaultRowHeightInPoints(40f);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        //HSSFCellStyle style = wb.createCellStyle();
        HSSFCell cell;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);

            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(style);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));//首行
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));//表头
        sheet.addMergedRegion(new CellRangeAddress(2, 11, 0, 0));//腾退土地面积
        sheet.addMergedRegion(new CellRangeAddress(12, 20, 0, 0));//拆除地上物面积
        sheet.addMergedRegion(new CellRangeAddress(21, 22, 0, 0));//村庄情况
        sheet.addMergedRegion(new CellRangeAddress(23, 27, 0, 0));//资金需求
        sheet.addMergedRegion(new CellRangeAddress(28, 36, 0, 0));//清理企业情况
        sheet.addMergedRegion(new CellRangeAddress(37, 38, 0, 0));//清理流动人口情况
        sheet.addMergedRegion(new CellRangeAddress(39, 42, 0, 0));//被拆除形态
        sheet.addMergedRegion(new CellRangeAddress(43, 47, 0, 0));//还绿情况
        //sheet.autoSizeColumn(1,true);
        sheet.setColumnWidth(0, 8000);
        sheet.setColumnWidth(1, 12000);
        sheet.setColumnWidth(2, 8000);

        //sheet.setColumnWidth(3, 25000);
        // sheet.setDefaultRowHeightInPoints((float)500);
        return wb;
    }
}
